unit uSyncDatabase;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DynamicSkinForm, uDM, spSkinShellCtrls, SkinCtrls, SkinBoxCtrls,
  spfilectrl, StdCtrls, Mask, Buttons, FileCtrl, ComCtrls, RzTreeVw, RzFilSys,
  RzButton, RzRadChk, StrUtils;

type
  TFrmSyncDatabase = class(TForm)
    spDynamicSkinForm1: TspDynamicSkinForm;
    btnLoadDir: TspSkinButton;
    FileListBox1: TspSkinFileListBox;
    spSkinButton1: TspSkinButton;
    DS: TRzDirectoryTree;
    spSkinStdLabel1: TspSkinStdLabel;
    LbListFile: TspSkinStdLabel;
    CheckBox1: TRzCheckBox;
    CheckBox2: TRzCheckBox;
    CheckBox3: TRzCheckBox;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnLoadDirClick(Sender: TObject);
    procedure spSkinButton1Click(Sender: TObject);
    procedure DSClick(Sender: TObject);
  private
    { Private declarations }
    procedure SyncNormal;
    procedure SyncUnicode;
    function GetVol(AVol: String): String;
  public
    { Public declarations }
  end;

var
  FrmSyncDatabase: TFrmSyncDatabase;

implementation

{$R *.dfm}

procedure TFrmSyncDatabase.btnLoadDirClick(Sender: TObject);
begin
  if not CheckBox3.Checked then
    SyncNormal else SyncUnicode;
//  if Confirm('Hapus History ..?') then DeleteFile(AFile);
end;

procedure TFrmSyncDatabase.DSClick(Sender: TObject);
begin
  FileListBox1.Directory := DS.Directory;
  LbListFile.Caption := 'List File pada direktory : '+DS.Directory; 
end;

procedure TFrmSyncDatabase.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action  := caFree;
end;

function TFrmSyncDatabase.GetVol(AVol:String) : String;
begin
  if AVol = 'L' then Result := 'LEFT';
  if AVol = 'R' then Result := 'RIGHT';
  if AVol = 'S' then Result := 'STEREO';
end;

procedure TFrmSyncDatabase.spSkinButton1Click(Sender: TObject);
begin
  Close;
end;

procedure TFrmSyncDatabase.SyncNormal;
var AFile, SQLFile: String;
begin
AFile := ExtractFilePath(Application.ExeName)+'banklagu.txt';
SQLFile := StringReplace(AFile,'\','/', [rfReplaceAll]);
//ShowMessage('AFile: '+AFile+', SQLFile :'+SQLFile);
//  FileListBox1.Directory := DS.Directory;
if CheckBox1.Checked = true then
begin
//  Inform('Checked');
  if FileListBox1.Items.Count > 0 then
  begin
      FileListBox1.Items.SaveToFile(AFile);
      ExecSQL(DM.Buffer,'TRUNCATE table song_list_tmp');
      ExecSQL(DM.Buffer,'LOAD DATA INFILE '+QuotedStr(SQLFile)+' INTO TABLE song_list_tmp FIELDS TERMINATED BY ''\#'' LINES TERMINATED BY ''\n''');
      if CheckBox2.Checked then ExecSQL(DM.Buffer,'TRUNCATE table song_list');
      ExecSQL(DM.Buffer,'INSERT INTO song_list (Judul,Artis,Bahasa,Jenis,Volume,Direktori) '+
      'SELECT Judul,Artis,Jenis,Jenis,left(Volume,1),'+QuotedStr(StringReplace(DS.Directory,'\','\\',[rfReplaceAll])+'\\')+' '+
      'FROM song_list_tmp WHERE not Volume is null');
//      ExecSQL(DM.Buffer,'UPDATE song_list SET NamaFile = concat(Judul,''#'',Artis,''#'',Bahasa,''#'',(IF(Volume=''L'',''LEFT'',''RIGHT'')),''.MPG'')');
      ExecSQL(DM.Buffer,'UPDATE song_list SET NamaFile = concat(Judul,''#'',Artis,''#'',Bahasa,''#'',CASE VOLUME WHEN ''L'' THEN ''LEFT'' WHEN ''R'' THEN ''RIGHT'' ELSE ''STEREO'' END ,''.MPG'')');
      ExecSQL(DM.Buffer,'UPDATE setting SET IpVideo ='+QuotedStr(ExtractFileDrive(DS.Directory))+',VideoDir='+QuotedStr('\\'+ExtractFileName(DS.Directory)+'\\'));
      Inform('Sync Database Sukses');
  end;
end else
begin
//  Inform('Not Checked');
  if FileListBox1.Items.Count > 0 then
  begin
    try
      FileListBox1.Items.SaveToFile(AFile);
      DM.Connection.StartTransaction;
      ExecSQL(DM.Buffer,'TRUNCATE table song_list_tmp');
      ExecSQL(DM.Buffer,'LOAD DATA INFILE '+SQLFile+' INTO TABLE song_list_tmp FIELDS TERMINATED BY ''\#'' LINES TERMINATED BY ''\n''');
      if CheckBox2.Checked then ExecSQL(DM.Buffer,'TRUNCATE table song_list');
      ExecSQL(DM.Buffer,'INSERT INTO song_list (Judul,Artis,Bahasa,Jenis,Volume,Direktori) '+
      'SELECT Judul,Artis,Jenis,Jenis,left(Volume,1),'+QuotedStr(StringReplace(DS.Directory,'\','\\',[rfReplaceAll])+'\\')+' '+
      'FROM song_list_tmp WHERE not Volume is null');
//      ExecSQL(DM.Buffer,'UPDATE song_list SET NamaFile = concat(Judul,''#'',Artis,''#'',Bahasa,''#'',(IF(Volume=''L'',''LEFT'',''RIGHT'')),''.MPG'')');
      ExecSQL(DM.Buffer,'UPDATE song_list SET NamaFile = concat(Judul,''#'',Artis,''#'',Bahasa,''#'',CASE VOLUME WHEN ''L'' THEN ''LEFT'' WHEN ''R'' THEN ''RIGHT'' ELSE ''STEREO'' END ,''.MPG'')');
      ExecSQL(DM.Buffer,'UPDATE setting SET IpVideo ='+QuotedStr(ExtractFileDrive(DS.Directory))+',VideoDir='+QuotedStr('\\'+ExtractFileName(DS.Directory)+'\\'));

      DM.Connection.Commit;
      Inform('Sync Database Sukses');
    except
      DM.Connection.Rollback;
      Alert('Sync Database Gagal');
    end;
  end;
end;
end;

procedure TFrmSyncDatabase.SyncUnicode;
var AFile, SQLFile: String;
    AMemStream : TMemoryStream;
begin
AMemStream := TMemoryStream.Create;
AFile := ExtractFilePath(Application.ExeName)+'banklagu.txt';
SQLFile := StringReplace(AFile,'\','/', [rfReplaceAll]);
if CheckBox1.Checked = true then
begin
//  Inform('Checked');
  if FileListBox1.Items.Count > 0 then
  begin
      FileListBox1.Items.SaveToFile(AFile);
      ExecSQL(DM.Buffer,'TRUNCATE table song_list_unicode');
      ExecSQL(DM.Buffer,'LOAD DATA INFILE '+QuotedStr(SQLFile)+' INTO TABLE song_list_unicode FIELDS TERMINATED BY ''\#'' LINES TERMINATED BY ''\n''');
      if CheckBox2.Checked then ExecSQL(DM.Buffer,'TRUNCATE table song_list');
      ExecSQL(DM.Buffer,'INSERT INTO song_list (Judul,Artis2,Artis,Judul2,Bahasa,Jenis,Volume,Direktori) '+
      'SELECT Judul,Judul2,Artis,Artis2,Jenis,Jenis,left(Volume,1),'+QuotedStr(StringReplace(DS.Directory,'\','\\',[rfReplaceAll])+'\\')+' '+
      'FROM song_list_unicode WHERE not Volume is null');
      ExecSQL(DM.Buffer,'UPDATE song_list SET NamaFile = concat(Judul,''#'',Judul2,''#'',Artis,''#'',Artis2,''#'',Bahasa,''#'',CASE VOLUME WHEN ''L'' THEN ''LEFT'' WHEN ''R'' THEN ''RIGHT'' ELSE ''STEREO'' END ,''.MPG'')');
      ExecSQL(DM.Buffer,'UPDATE setting SET IpVideo ='+QuotedStr(ExtractFileDrive(DS.Directory))+',VideoDir='+QuotedStr('\\'+ExtractFileName(DS.Directory)+'\\'));
      Inform('Sync Database Unicode Sukses');
  end;
end else
begin
//  Inform('Not Checked');
  if FileListBox1.Items.Count > 0 then
  begin
    try
      FileListBox1.Items.SaveToFile(AFile);
      DM.Connection.StartTransaction;
      ExecSQL(DM.Buffer,'TRUNCATE table song_list_unicode');
      ExecSQL(DM.Buffer,'LOAD DATA INFILE '+QuotedStr(SQLFile)+' INTO TABLE song_list_unicode FIELDS TERMINATED BY ''\#'' LINES TERMINATED BY ''\n''');
      if CheckBox2.Checked then ExecSQL(DM.Buffer,'TRUNCATE table song_list');
      ExecSQL(DM.Buffer,'INSERT INTO song_list (Judul,Artis2,Artis,Judul2,Bahasa,Jenis,Volume,Direktori) '+
      'SELECT Judul,Judul2,Artis,Artis2,Jenis,Jenis,left(Volume,1),'+QuotedStr(StringReplace(DS.Directory,'\','\\',[rfReplaceAll])+'\\')+' '+
      'FROM song_list_unicode WHERE not Volume is null');
      ExecSQL(DM.Buffer,'UPDATE song_list SET NamaFile = concat(Judul,''#'',Judul2,''#'',Artis,''#'',Artis2,''#'',Bahasa,''#'',CASE VOLUME WHEN ''L'' THEN ''LEFT'' WHEN ''R'' THEN ''RIGHT'' ELSE ''STEREO'' END ,''.MPG'')');
      ExecSQL(DM.Buffer,'UPDATE setting SET IpVideo ='+QuotedStr(ExtractFileDrive(DS.Directory))+',VideoDir='+QuotedStr('\\'+ExtractFileName(DS.Directory)+'\\'));

      DM.Connection.Commit;
      Inform('Sync Database Unicode Sukses');
    except
      DM.Connection.Rollback;
      Alert('Sync Database Unicode Gagal');
    end;
  end;
end;
end;

end.
